﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace GSManager
{
    public class PhieuNo
    {
        private int _SoPhieuNo;
        private int _SoPhieuBan;
        private string _MaKhachHang;
        private DateTime _NgayNo;
        private DateTime _NgayTra;
        private decimal _SoTienTra;
        private decimal _NoDu;
        private string _NguoiLapPhieu;
        public int SoPhieuNo
        {
            get { return _SoPhieuNo; }
            set { _SoPhieuNo = value; }
        }
        public int SoPhieuBan
        {
            get { return _SoPhieuBan; }
            set { _SoPhieuBan = value; }
        }
        public string MaKhachHang
        {
            get { return _MaKhachHang; }
            set { _MaKhachHang = value; }
        }
        public DateTime NgayNo
        {
            get { return _NgayNo; }
            set { _NgayNo = value; }
        }
        public DateTime NgayTra
        {
            get { return _NgayTra; }
            set { _NgayTra = value; }
        }
        public decimal SoTienTra
        {
            get { return _SoTienTra; }
            set { _SoTienTra = value; }
        }
        public decimal NoDu
        {
            get { return _NoDu; }
            set { _NoDu = value; }
        }
        public string NguoiLapPhieu
        {
            get { return _NguoiLapPhieu; }
            set { _NguoiLapPhieu = value; }
        }
        public void ThemPhieuNo()
        {
            OleDbConnection connect = KetNoi.KetNoiDuLieu();
            string sql = "Insert into PHIEUNO values(" + SoPhieuNo + "," + SoPhieuBan + ",'" + MaKhachHang + "',?,?," + SoTienTra + "," + NoDu + ",?)";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            cmd.Parameters.Add("@NgayNo", OleDbType.Date);
            cmd.Parameters.Add("@NgayTra", OleDbType.Date);
            cmd.Parameters.Add("@NguoiLapPhieu", OleDbType.VarWChar);
            cmd.Parameters[0].Value = NgayNo;
            cmd.Parameters[1].Value = NgayTra;
            cmd.Parameters[2].Value = NguoiLapPhieu;
            cmd.ExecuteNonQuery();
            connect.Close();
        }
        public int DemSoPhieuNo(int sophieuban)
        {
            OleDbConnection connect = KetNoi.KetNoiDuLieu();
            string sql = "select Count(SoPhieuNo) from PHIEUNO where SoPhieuBan="+sophieuban+"";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            OleDbDataReader r = cmd.ExecuteReader();
            int kq = 0;
            while (r.Read())
            {
                try
                {
                    kq = (int)r[0];
                }
                catch (Exception) { }
            }
            r.Close();
            connect.Close();
            return kq;
        }
        public DataSet LayDanhSachPhieuNo(string makh)
        {
            OleDbConnection connect = KetNoi.KetNoiDuLieu();
            string sql = "select * from PHIEUNO where MaKhachHang='" + makh + "' and NoDu>0 and SoPhieuNo in (select Max(SoPhieuNo) from PHIEUNO where MaKhachHang='" + makh + "'group by SoPhieuBan)  and SoPhieuBan not in(select SoPhieuBan from PHIEUNO where NoDu=0) group by SoPhieuBan,SoPhieuNo,MaKhachHang,NgayNo,NgayTra,SoTienTra,NoDu,NguoiLapPhieu";
            DataSet ds = new DataSet();
            OleDbDataAdapter a = new OleDbDataAdapter(sql, connect);
            a.Fill(ds, "PHIEUNO");
            connect.Close();
            return ds;
        }
    }
}
